Apache Cayenne Documentation - Iterating Through Data Rows

There are cases when the result sets are so large that even when fetching data rows, application can run out of memory. For instance, a user may be creating a report that requires in-memory processing of hundreds of thousands of database rows. In such cases normal Cayenne behavior of reading the whole java.sql.ResultSet in the memory before returning it to the user may result in an application exhausing all memory and crashing.

Cayenne solves this by allowing to obtain results in the form of ResultIterator. ResultIterator is connected to an open java.sql.ResultSet, therefore its methods may throw checked exceptions. ResultIterator returns data rows (not DataObjects) one at a time, reading them on demand from the open ResultSet. Each data row can be converted to a DataObject or accessed directly. Open ResultIterator locks the database connection, therefore ResultIterator always requires explicit closing in the user code.

In web applications, programmers must ensure that no open ResultIterators are kept between HTTP requests. Failure to do so may result in too many database connections being locked, thus quickly exhausting connection pool. In general, an application with Web GUI is NOT a good candidate for implementation using ResultIterators.

When working with open ResultIterator, users still can perform any other database operations: select queries, traversing object relationships, etc. Any parallel data operation will internally check out an unused connection from the connection pool as it would normally do, while ResultIterator still locks its own connection.

ResultIterator annotated example:

import java.util.List;
import java.util.Map;
import org.apache.cayenne.access.DataContext;
import org.apache.cayenne.access.ResultIterator;
import org.apache.cayenne.query.SelectQuery;
import org.apache.cayenne.CayenneException;
import org.apache.art.Artist;
...
DataContext ctxt;

// create a usual query
SelectQuery q = new SelectQuery(Artist.class);
ResultIterator it = null;

// ResultIterator operations all throw checked CayenneException
try {
   // special "performIteratedQuery" method is used
   it = ctxt.performIteratedQuery(q);

   while(it.hasNextRow()) {
      // ResultIterator always returns data rows 
      Map row = it.nextDataRow();
      
      // do something with the row...
      ...
   }
}
catch(CayenneException ex) {
   ex.printStackTrace();
}
finally {
   try {
      // explicit closing of the iterator is required !!!
      it.close();
   }
   catch(CayenneException closeEx) {
       closeEx.printStackTrace();
   }
}
...